create table ThanhToan
(
	ma_hoa_don int,
	ngay_thanh_toan datetime,
	so_tien int
)

go

create proc sp_ThanhToan_Insert
(
	@ma_hoa_don int,
	@so_tien int
)
as 
begin
	DECLARE @ngay_cap_nhat_cuoi datetime
	SET @ngay_cap_nhat_cuoi = getdate()
	
	insert into ThanhToan values (@ma_hoa_don,@ngay_cap_nhat_cuoi,@so_tien)
end

go

create trigger tg_ThanhToan_Insert
on ThanhToan
after insert
as
begin
	declare @tongtien int,@ma_hoa_don int
	set @ma_hoa_don = (select ma_hoa_don from inserted)
	set @tongtien = (select sum(so_tien) from thanhtoan where ma_hoa_don = @ma_hoa_don)  

	if (select tong_tien from hoadon where id=@ma_hoa_don) <= @tongtien
		update hoadon set status = 'True',tong_tien=@tongtien where id=@ma_hoa_don
end

go

create proc sp_CongNo_GetAll
	@ma_khach_hang int
as
begin
	select id,tong_tien,ngay_lap,sum(so_tien) as thanhtoan from HoaDon hd inner join thanhtoan tt on hd.id = tt.ma_hoa_don
	where hd.ma_khach_hang = @ma_khach_hang and status = 'False'
	group by id,tong_tien,ngay_lap
end